package kz.asoft.am;


import de.congrace.exp4j.Calculable;
import de.congrace.exp4j.ExpressionBuilder;

import java.math.BigDecimal;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import kz.asoft.am.common.RisksAppModule;
import kz.asoft.model.GRiskCalcsModelImpl;
import kz.asoft.model.GRiskValuesModelImpl;
import kz.asoft.model.GRisksCalcsObjModelImpl;
import kz.asoft.model.GRisksModelImpl;
import kz.asoft.model.RDataPeriodModelImpl;
import kz.asoft.model.RDataValue1ModelImpl;
import kz.asoft.model.RDataValue2ModelImpl;
import kz.asoft.model.SRiskListValuesModelImpl;
import kz.asoft.model.SRiskValuesModelImpl;
import kz.asoft.view.MonthListViewImpl;

import oracle.adf.share.ADFContext;

import oracle.jbo.JboException;
import oracle.jbo.Key;
import oracle.jbo.Row;
import oracle.jbo.RowSet;
import oracle.jbo.RowSetIterator;
import oracle.jbo.Session;
import oracle.jbo.ViewObject;
import oracle.jbo.domain.Number;
import oracle.jbo.server.ApplicationModuleImpl;
import oracle.jbo.server.SequenceImpl;
import oracle.jbo.server.ViewLinkImpl;
import oracle.jbo.server.ViewObjectImpl;


 import org.apache.log4j.Logger;


// ---------------------------------------------------------------------
// ---    File generated by Oracle ADF Business Components Design Time.
// ---    Sat Oct 05 15:23:31 UZT 2013
// ---    Custom code may be added to this class.
// ---    Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class RisksAppModuleImpl extends ApplicationModuleImpl implements RisksAppModule {
    private static Logger LOGGER = Logger.getLogger(RisksAppModuleImpl.class.getName());
    
    /**
     * This is the default constructor (do not remove).
     */
    public RisksAppModuleImpl() {
    }

    /**
     * Container's getter for SCtrlObjGroupsView1.
     * @return SCtrlObjGroupsView1
     */
    public ViewObjectImpl getSCtrlObjGroupsView1() {
        return (ViewObjectImpl)findViewObject("SCtrlObjGroupsView1");
    }

    /**
     * Container's getter for SCtrlObjsView1.
     * @return SCtrlObjsView1
     */
    public ViewObjectImpl getSCtrlObjsView1() {
        return (ViewObjectImpl)findViewObject("SCtrlObjsView1");
    }

    /**
     * Container's getter for GRisksCalcsObjView1.
     * @return GRisksCalcsObjView1
     */
    public ViewObjectImpl getGRisksCalcsObjView1() {
        return (ViewObjectImpl)findViewObject("GRisksCalcsObjView1");
    }

    /**
     * Container's getter for GRiskValuesView1.
     * @return GRiskValuesView1
     */
    public ViewObjectImpl getGRiskValuesView1() {
        return (ViewObjectImpl)findViewObject("GRiskValuesView1");
    }


    /**
     * Container's getter for SPublicDataTypeView1.
     * @return SPublicDataTypeView1
     */
    public ViewObjectImpl getSPublicDataTypeView1() {
        return (ViewObjectImpl)findViewObject("SPublicDataTypeView1");
    }

    /**
     * Container's getter for SUnitsView1.
     * @return SUnitsView1
     */
    public ViewObjectImpl getSUnitsView1() {
        return (ViewObjectImpl)findViewObject("SUnitsView1");
    }

    /**
     * Container's getter for GRisksCurrentView1.
     * @return GRisksCurrentView1
     */
    public ViewObjectImpl getGRisksCurrentView1() {
        return (ViewObjectImpl)findViewObject("GRisksCurrentView1");
    }

    /**
     * Container's getter for GRisksArchiveView1.
     * @return GRisksArchiveView1
     */
    public ViewObjectImpl getGRisksArchiveView1() {
        return (ViewObjectImpl)findViewObject("GRisksArchiveView1");
    }

    /**
     * Container's getter for SRiskValuesView1.
     * @return SRiskValuesView1
     */
    public ViewObjectImpl getSRiskValuesView1() {
        return (ViewObjectImpl)findViewObject("SRiskValuesView1");
    }

    /**
     * Container's getter for SCtrlObjsLink1.
     * @return SCtrlObjsLink1
     */
    public ViewLinkImpl getSCtrlObjsLink1() {
        return (ViewLinkImpl)findViewLink("SCtrlObjsLink1");
    }

    /**
     * Container's getter for CtrlObjRisksResultsLink1.
     * @return CtrlObjRisksResultsLink1
     */
    public ViewLinkImpl getCtrlObjRisksResultsLink1() {
        return (ViewLinkImpl)findViewLink("CtrlObjRisksResultsLink1");
    }

    /**
     * Container's getter for CtrlObjRisksResultValuesLink1.
     * @return CtrlObjRisksResultValuesLink1
     */
    public ViewLinkImpl getCtrlObjRisksResultValuesLink1() {
        return (ViewLinkImpl)findViewLink("CtrlObjRisksResultValuesLink1");
    }

    /**
     * Container's getter for RDataValue1Link1.
     * @return RDataValue1Link1
     */
    public ViewLinkImpl getRDataValue1Link1() {
        return (ViewLinkImpl)findViewLink("RDataValue1Link1");
    }

    /**
     * Container's getter for RDataValue2Link1.
     * @return RDataValue2Link1
     */
    public ViewLinkImpl getRDataValue2Link1() {
        return (ViewLinkImpl)findViewLink("RDataValue2Link1");
    }

    /**
     * Container's getter for RDataTypeLink1.
     * @return RDataTypeLink1
     */
    public ViewLinkImpl getRDataTypeLink1() {
        return (ViewLinkImpl)findViewLink("RDataTypeLink1");
    }

    /**
     * Container's getter for SUnitsLink1.
     * @return SUnitsLink1
     */
    public ViewLinkImpl getSUnitsLink1() {
        return (ViewLinkImpl)findViewLink("SUnitsLink1");
    }

    /**
     * Container's getter for GRisksCurrentLink1.
     * @return GRisksCurrentLink1
     */
    public ViewLinkImpl getGRisksCurrentLink1() {
        return (ViewLinkImpl)findViewLink("GRisksCurrentLink1");
    }

    /**
     * Container's getter for GRisksArchiveLink1.
     * @return GRisksArchiveLink1
     */
    public ViewLinkImpl getGRisksArchiveLink1() {
        return (ViewLinkImpl)findViewLink("GRisksArchiveLink1");
    }
    
    public ViewObjectImpl getGRiskCalcsView1() {
        return (ViewObjectImpl)findViewObject("GRiskCalcsView1");
    }

    /**
     * Container's getter for SRisksValuesLink1.
     * @return SRisksValuesLink1
     */
    public ViewLinkImpl getSRisksValuesLink1() {
        return (ViewLinkImpl)findViewLink("SRisksValuesLink1");
    }
    
    /**
     * Container's getter for SRiskGroupValuesView1.
     * @return SRiskGroupValuesView1
     */
    public ViewObjectImpl getSRiskGroupValuesView1() {
        return (ViewObjectImpl)findViewObject("SRiskGroupValuesView1");
    }
    
    /**
     * Container's getter for SRiskListValuesView1.
     * @return SRiskListValuesView1
     */
    public ViewObjectImpl getSRiskListValuesView1() {
        return (ViewObjectImpl)findViewObject("SRiskListValuesView1");
    }

    /**
     * Container's getter for SRiskListValuesLink1.
     * @return SRiskListValuesLink1
     */
    public ViewLinkImpl getSRiskListValuesLink1() {
        return (ViewLinkImpl)findViewLink("SRiskListValuesLink1");
    }
    
    /**
     * Container's getter for SRiskListValuesView2.
     * @return SRiskListValuesView2
     */
    public ViewObjectImpl getSRiskListValuesView2() {
        return (ViewObjectImpl)findViewObject("SRiskListValuesView2");
    }

    /**
     * Container's getter for GRisksEditView1.
     * @return GRisksEditView1
     */
    public ViewObjectImpl getGRisksEditView1() {
        return (ViewObjectImpl)findViewObject("GRisksEditView1");
    }

    /**
     * Container's getter for SRiskValuesView2.
     * @return SRiskValuesView2
     */
    public ViewObjectImpl getSRiskValuesView2() {
        return (ViewObjectImpl)findViewObject("SRiskValuesView2");
    }

    /**
     * Container's getter for SRiskGroupsView1.
     * @return SRiskGroupsView1
     */
    public ViewObjectImpl getSRiskGroupsView1() {
        return (ViewObjectImpl)findViewObject("SRiskGroupsView1");
    }

    /**
     * Container's getter for OperListOfValuesView1.
     * @return OperListOfValuesView1
     */
    public ViewObjectImpl getOperListOfValuesView1() {
        return (ViewObjectImpl)findViewObject("OperListOfValuesView1");
    }

    /**
     * Container's getter for SRiskCreateValuesView1.
     * @return SRiskCreateValuesView1
     */
    public ViewObjectImpl getSRiskCreateValuesView1() {
        return (ViewObjectImpl)findViewObject("SRiskCreateValuesView1");
    }

    /**
     * Container's getter for SRiskCreateListValuesView1.
     * @return SRiskCreateListValuesView1
     */
    public ViewObjectImpl getSRiskCreateListValuesView1() {
        return (ViewObjectImpl)findViewObject("SRiskCreateListValuesView1");
    }

    /**
     * Container's getter for GRisksListView1.
     * @return GRisksListView1
     */
    public ViewObjectImpl getGRisksListView1() {
        return (ViewObjectImpl)findViewObject("GRisksListView1");
    }
    
    /**
     * Container's getter for SPublicDataView1.
     * @return SPublicDataView1
     */
    public ViewObjectImpl getSPublicDataView1() {
        return (ViewObjectImpl)findViewObject("SPublicDataView1");
    }

    /**
     * Container's getter for SPublicDataTypePublicDataViewLink1.
     * @return SPublicDataTypePublicDataViewLink1
     */
    public ViewLinkImpl getSPublicDataTypePublicDataViewLink1() {
        return (ViewLinkImpl)findViewLink("SPublicDataTypePublicDataViewLink1");
    }

    /**
     * Container's getter for GCoverCtrlObjView1.
     * @return GCoverCtrlObjView1
     */
    public ViewObjectImpl getGCoverCtrlObjView1() {
        return (ViewObjectImpl)findViewObject("GCoverCtrlObjView1");
    }

    /**
     * Container's getter for GPlntdThemCtrlView1.
     * @return GPlntdThemCtrlView1
     */
    public ViewObjectImpl getGPlntdThemCtrlView1() {
        return (ViewObjectImpl)findViewObject("GPlntdThemCtrlView1");
    }
    
    /**
     * Container's getter for GPlntdThemALLCtrlView1.
     * @return GPlntdThemALLCtrlView1
     */
    public ViewObjectImpl getGPlntdThemALLCtrlView1() {
        return (ViewObjectImpl)findViewObject("GPlntdThemALLCtrlView1");
    }

    /**
     * Container's getter for RDataPeriodView1.
     * @return RDataPeriodView1
     */
    public ViewObjectImpl getRDataPeriodView1() {
        return (ViewObjectImpl)findViewObject("RDataPeriodView1");
    }

    /**
     * Container's getter for SCtrlObjRiskReportView1.
     * @return SCtrlObjRiskReportView1
     */
    public ViewObjectImpl getSCtrlObjRiskReportView1() {
        return (ViewObjectImpl)findViewObject("SCtrlObjRiskReportView1");
    }

    /**
     * Container's getter for SCtrlObjRiskReportLink1.
     * @return SCtrlObjRiskReportLink1
     */
    public ViewLinkImpl getSCtrlObjRiskReportLink1() {
        return (ViewLinkImpl)findViewLink("SCtrlObjRiskReportLink1");
    }

    /**
     * Container's getter for RDataValueByFormulaView1.
     * @return RDataValueByFormulaView1
     */
    public ViewObjectImpl getRDataValueByFormulaView1() {
        return (ViewObjectImpl)findViewObject("RDataValueByFormulaView1");
    }
    /**
     * Container's getter for CtrlObjDataPeriodViewLink1.
     * @return CtrlObjDataPeriodViewLink1
     */
    public ViewLinkImpl getCtrlObjDataPeriodViewLink1() {
        return (ViewLinkImpl)findViewLink("CtrlObjDataPeriodViewLink1");
    }

    /**
     * Container's getter for RDataValue1View1.
     * @return RDataValue1View1
     */
    public ViewObjectImpl getRDataValue1View1() {
        return (ViewObjectImpl)findViewObject("RDataValue1View1");
    }

    /**
     * Container's getter for RDataValue2View1.
     * @return RDataValue2View1
     */
    public ViewObjectImpl getRDataValue2View1() {
        return (ViewObjectImpl)findViewObject("RDataValue2View1");
    }
    
    /**
     * Container's getter for GRisksArchiveByParentView1.
     * @return GRisksArchiveByParentView1
     */
    public ViewObjectImpl getGRisksArchiveByParentView1() {
        return (ViewObjectImpl)findViewObject("GRisksArchiveByParentView1");
    }

    /**
     * Container's getter for MonthListView1.
     * @return MonthListView1
     */
    public ViewObjectImpl getMonthListView1() {
        return (ViewObjectImpl)findViewObject("MonthListView1");
    }
    
     /**
     * Добавление риска
     */
    public void addRisk() {
        ViewObject riskVO = getGRisksEditView1();

        Row riskRow = riskVO.getCurrentRow();
        
        Number newRiskId = getRiskNextSeq();
        Number currRiskId = (Number)riskRow.getAttribute("Id");
        if (currRiskId != null) {
            Row newRisk = riskVO.createRow();
            newRisk.setAttribute("Id", newRiskId);
            newRisk.setAttribute("NameRu", riskRow.getAttribute("NameRu"));
            newRisk.setAttribute("NameKz", riskRow.getAttribute("NameKz"));
            newRisk.setAttribute("DescRu", riskRow.getAttribute("DescRu"));
            newRisk.setAttribute("DescKz", riskRow.getAttribute("DescKz"));
            newRisk.setAttribute("Formula", riskRow.getAttribute("Formula"));
            newRisk.setAttribute("CtrlObjGroupId", riskRow.getAttribute("CtrlObjGroupId"));
            
            newRisk.setAttribute("Version", ((Number)riskRow.getAttribute("Version")).increment());
            newRisk.setAttribute("OpenDate", new Date());
            
            riskRow.refresh(Row.REFRESH_UNDO_CHANGES);
            riskRow.setAttribute("ParentId", newRiskId);
        } else {
            ViewObject groupsVO = getSCtrlObjGroupsView1();
            Row groupRow = groupsVO.getCurrentRow();
            riskRow.setAttribute("CtrlObjGroupId", groupRow.getAttribute("Id"));
            riskRow.setAttribute("Id", newRiskId);
            riskRow.setAttribute("OpenDate", new Date());
        }

        ViewObject createValVO = getSRiskCreateValuesView1();
        Row[] rows = createValVO.getAllRowsInRange();
        ViewObject riskListVO = getSRiskCreateListValuesView1();
        for (Row row : rows) {
            Integer riskListValId = null;
            // если заполнены поля то создаем списки для значений рисков
            if (row.getAttribute("ListValNameRu") != null && !"".equals(row.getAttribute("ListValNameRu")) ||
                        row.getAttribute("ListValNameKz") != null && !"".equals(row.getAttribute("ListValNameKz"))) {
                riskListValId = getRiskListValNextSeq().intValue();
                Row newRiskListValRow = riskListVO.createRow();
                newRiskListValRow.setAttribute("Id", riskListValId);
                newRiskListValRow.setAttribute("NameRu", row.getAttribute("ListValNameRu"));
                newRiskListValRow.setAttribute("NameKz", row.getAttribute("ListValNameKz"));
                newRiskListValRow.setAttribute("Value", row.getAttribute("Value1"));
                newRiskListValRow.setAttribute("RiskId", newRiskId);
                newRiskListValRow.setAttribute("RiskGroupId", row.getAttribute("Id"));
            }
            ViewObject riskValueVO = getSRiskValuesView1();
            Row newRiskValRow = riskValueVO.createRow();
            newRiskValRow.setAttribute("Id", getRiskValNextSeq());
            newRiskValRow.setAttribute("Oper", row.getAttribute("Oper"));
            newRiskValRow.setAttribute("Value1", row.getAttribute("Value1"));
            newRiskValRow.setAttribute("Value2", row.getAttribute("Value2"));
            newRiskValRow.setAttribute("RiskGroupId", row.getAttribute("Id"));
            newRiskValRow.setAttribute("RiskId", newRiskId);

            if (riskListValId != null) {
                newRiskValRow.setAttribute("RiskListValueId", riskListValId);
            }
        }
        getDBTransaction().commit();
    }
     
     /**
      * Удаление риска
      * @param riskId
      */
     public void deleteRisk(Number riskId) {
         ViewObject risksVO = getGRisksCurrentView1();
         Row[] r = risksVO.findByKey(new Key(new Object[]{riskId}), 1);
         if (r.length > 0) {
             Row delRow = r[0];
             delRow.setAttribute("CloseDate", new Date());
             getDBTransaction().commit();
         }
     }

    /**
     * Подсчет коэффициента
     * @param groupId - Id группы объектов контроля
     * @return ratio - Подсчитанный коеффициент
     */
    public Double calculateRatio(Number groupId) {
        ViewObject vo = getGRisksListView1();
        ViewObject riskGroupsVO = getSRiskGroupsView1();
        vo.setWhereClause("CTRL_OBJ_GROUP_ID = " + groupId +
                          " AND PARENT_ID is NULL");
        vo.executeQuery();

        // следующий порядковый номер риска
        double riskNumber = vo.getRowCount() + 1;

        double risksCount = riskGroupsVO.getRowCount(); // vo.getRowCount();
        double ratio = 1 + (1 / (risksCount - 1)) * (riskNumber - 1);

        return ratio;
    }
    
    /**
     * Восстановление риска
     */
    public Number restoreRiskFromArchive(Number restoreRiskId) {
        ViewObject riskListVO = getGRisksEditView1();
        riskListVO.setWhereClause("GRisksModel.ID = " + restoreRiskId);
        riskListVO.executeQuery();
        Row restoreRiskRow = riskListVO.getRowSet().first();
        
        ViewObject archiveParentVO = getGRisksArchiveParentView1();
        archiveParentVO.setNamedWhereClauseParam("childId", restoreRiskId);
        archiveParentVO.executeQuery();
        Row lastRiskRow = archiveParentVO.first();
        
        lastRiskRow.setAttribute("ParentId", restoreRiskRow.getAttribute("Id"));
        restoreRiskRow.setAttribute("ParentId", "");
        
//        Историей восстановления риска...
//        Number newRiskVersion = ((Number) lastRiskRow.getAttribute("Version")).add(1);
//        Number newRiskId = getRiskNextSeq();
//        lastRiskRow.setAttribute("ParentId", newRiskId);
//        
//        Row newRiskRow = riskListVO.createRow();
//        newRiskRow.setAttribute("Id", newRiskId);
//        newRiskRow.setAttribute("NameRu", restoreRiskRow.getAttribute("NameRu"));
//        newRiskRow.setAttribute("NameKz", restoreRiskRow.getAttribute("NameKz"));
//        newRiskRow.setAttribute("DescRu", restoreRiskRow.getAttribute("DescRu"));
//        newRiskRow.setAttribute("DescKz", restoreRiskRow.getAttribute("DescKz"));
//        newRiskRow.setAttribute("CtrlObjGroupId", restoreRiskRow.getAttribute("CtrlObjGroupId"));
//        newRiskRow.setAttribute("Version", restoreRiskRow.getAttribute("Version"));
//        newRiskRow.setAttribute("OpenDate", new Date());
//        
//        ViewObject valuesVO = getSRiskValuesView2();
//        valuesVO.setWhereClause("RISK_ID = " + restoreRiskId);
//        valuesVO.setOrderByClause("RISK_GROUP_ID");
//        valuesVO.executeQuery();
//        RowSet riskValuesRowSet = valuesVO.getRowSet();
//        
//        ViewObject listValuesVO = getSRiskListValuesView2();
//        listValuesVO.setWhereClause("RISK_ID = " + restoreRiskId);
//        listValuesVO.setOrderByClause("RISK_GROUP_ID");
//        listValuesVO.executeQuery();
//        RowSet riskListValuesRowSet = listValuesVO.getRowSet();
//        
//        while(riskValuesRowSet.hasNext()) {
//            Row curValRow = riskValuesRowSet.next();
//            
//            Number newId = null;
//            if (curValRow.getAttribute("RiskListValueId") != null) {
//                Row curListValRow = riskListValuesRowSet.next();
//                Row listValRow = listValuesVO.createRow();
//                newId = getRiskListValNextSeq();
//                listValRow.setAttribute("Id", newId);
//                listValRow.setAttribute("RiskId", newRiskId);
//                listValRow.setAttribute("Value", curListValRow.getAttribute("Value"));
//                listValRow.setAttribute("NameRu", curListValRow.getAttribute("NameRu"));
//                listValRow.setAttribute("NameKz", curListValRow.getAttribute("NameKz"));
//                listValRow.setAttribute("RiskGroupId", curListValRow.getAttribute("RiskGroupId"));
//            }
//           
//            Row newValRow = valuesVO.createRow();
//            newValRow.setAttribute("Id", getRiskValNextSeq());
//            newValRow.setAttribute("RiskId", newRiskId);
//            newValRow.setAttribute("RiskGroupId", curValRow.getAttribute("RiskGroupId"));
//            newValRow.setAttribute("Oper", curValRow.getAttribute("Oper"));
//            newValRow.setAttribute("Value1", curValRow.getAttribute("Value1"));
//            newValRow.setAttribute("Value2", curValRow.getAttribute("Value2"));
//            if (newId != null) {
//                newValRow.setAttribute("RiskListValueId", newId);
//            }
//        }
        
        getTransaction().commit();
        
        return (Number) restoreRiskRow.getAttribute("Id");
    }

    /**
     * Формирование БД для анализа рисков, анализ рисков
     */
    public void createFormData() {
        //Получение анализов рисков за последние 2 года
        Number ctrlObjId =
            (Number)getSCtrlObjsView1().getCurrentRow().getAttribute("Id");
        int year = Calendar.getInstance().get(Calendar.YEAR);
        ViewObject gRiskCalcVO = getCtrlObjRisksResultsLink1().getDestination();
        gRiskCalcVO.setWhereClause(" (YEAR BETWEEN " + (year - 2) + " AND " + year + ") ");
        gRiskCalcVO.executeQuery();
        Row lastRiskCalc = gRiskCalcVO.last();

        //Период анализа рисков
        Number riskCalcId = getRiskCalcsNextSeq();
        Row newRiskCalcsRow = getGRiskCalcsView1().createRow();
        newRiskCalcsRow.setAttribute("Id", riskCalcId);
        newRiskCalcsRow.setAttribute("Year", year);
        newRiskCalcsRow.setAttribute("OpenDate", new Date());

        //Результат анализа риска
        Number riskCalcObjId = getRiskCalcsObjNextSeq();
        ViewObject riskCalcsObjVO = getGRisksCalcsObjView1();
        Row newRiskCalcsObjRow = riskCalcsObjVO.createRow();
        newRiskCalcsObjRow.setAttribute("Id", riskCalcObjId);
        newRiskCalcsObjRow.setAttribute("RcyId", riskCalcId);
        newRiskCalcsObjRow.setAttribute("CtrlObjId", ctrlObjId);

        RowSetIterator rsRisksIterator =
            getGRisksCurrentLink1().getDestination().createRowSetIterator(null);

        double resultRiskScoring = 0.0;
        double riskValue = 0.0;
        int riskListValue = 0;
        double totalScoring = rsRisksIterator.getRowCount() * 5;

        while (rsRisksIterator.hasNext()) {
            Row riskRow = rsRisksIterator.next();
            RowSet riskValuesRS = (RowSet)riskRow.getAttribute("SRiskValuesView");
            riskValue = Double.valueOf(riskRow.getAttribute("RiskValue") == null ? "0" :
                                   riskRow.getAttribute("RiskValue").toString());
            riskListValue = Integer.valueOf(riskRow.getAttribute("RiskValueId") == null ? "0" :
                                riskRow.getAttribute("RiskValueId").toString());
            if(riskListValue > 0){
                riskValue = riskListValue;   
            }
            if (riskValuesRS.getRowCount() > 0) {
                Row newRiskValuesRow = getGRiskValuesView1().createRow();
                Number gRiskValueId = getGRiskValuesNextSeq();
                newRiskValuesRow.setAttribute("Id", gRiskValueId);
                newRiskValuesRow.setAttribute("RiskClacId", riskCalcObjId);
                newRiskValuesRow.setAttribute("RiskValue", riskValue);
                //Установка риска, группы риска по введённому показателю
                Map<String, Object> riskMap = getCalculatedValues(riskValuesRS, riskValue, riskListValue);
                newRiskValuesRow.setAttribute("RiskValueId", riskMap.get("id"));
                newRiskValuesRow.setAttribute("RiskGroupId", riskMap.get("groupId"));
                //Подсчёт процентной доли
                resultRiskScoring += Double.valueOf(riskMap.get("code").toString());
            }
        }
        //Установка группы риска объекту контроля
        getSRiskGroupValuesView1().executeQuery();
        RowSet riskGroupValuesRS = getSRiskGroupValuesView1().getRowSet();
        resultRiskScoring = resultRiskScoring / totalScoring * 100;
        Map<String, Object> riskMap = getCalculatedValues(riskGroupValuesRS, resultRiskScoring, riskListValue);
        //Проверка наличия анализов рисков за последние 2 года
        if (lastRiskCalc == null) {
            newRiskCalcsObjRow.setAttribute("ResultRiskGroupId", riskMap.get("groupId"));
        } else {
            Row row = riskGroupValuesRS.first();
            newRiskCalcsObjRow.setAttribute("ResultRiskGroupId", row.getAttribute("RiskGroupId"));
        }

        getTransaction().commit();
        riskCalcsObjVO.executeQuery();
    }
    
    /**
     * Получение расчитанного значения риска, группы риска, значения кода для подсчёта процентной доли
     * @param rowSet - RowSet of risk values, group values
     * @param compareValue - Input risk value
     * @param compareIdValue - Selected risk list value
     * @return
     */
    private Map<String, Object> getCalculatedValues(RowSet rowSet, double compareValue, int compareIdValue) {
        Map<String, Object> map = new HashMap<String, Object>();
        while (rowSet.hasNext()) {
            Row row = rowSet.next();
            double value1 = Double.valueOf(row.getAttribute("Value1") == null ? "0" :
                               row.getAttribute("Value1").toString());
            double value2 = Double.valueOf(row.getAttribute("Value2") == null ? "0" :
                               row.getAttribute("Value2").toString());
            int oper = Integer.valueOf(row.getAttribute("Oper") == null ? "-1" :
                                row.getAttribute("Oper").toString());
            //Установка риска, группы риска, кода
            map.put("id", row.getAttribute("Id"));
            map.put("groupId", row.getAttribute("RiskGroupId"));
            map.put("code", row.getAttribute("Code"));
            
            if (oper < 0) {
                int riskListId = Integer.valueOf(row.getAttribute("RiskListValueId").toString());
                if (compareIdValue == riskListId) {
                    break;
                }
            } else if (oper == 0) {
                if (compareValue > value1) {
                    break;
                }
            } else if (oper == 1) {
                if (compareValue < value1) {
                    break;
                }
            } else if (oper == 2) {
                if (compareValue <= value1) {
                    break;
                }
            } else if (oper == 3) {
                if (compareValue >= value1) {
                    break;
                }
            } else if (oper == 4) {
                if (compareValue == value1) {
                    break;
                }
            } else if (oper == 5) {
                if (compareValue != value1) {
                    break;
                }
            } else if (oper == 6) {
                if (compareValue >= value1 && compareValue <= value2) {
                    break;
                }
            }
        }
        return map;
    }
    
    /**
     * Поиск по наименованию объекта контроля
     * @param searchText
     */
    public void searchCtrlObj(String searchText) {
        ViewObject searchVO = getSCtrlObjsView1();
        searchVO.setWhereClause("(UPPER(NAME) LIKE UPPER('%" + searchText + "%'))");
        searchVO.executeQuery();
    }
    
    /**
     * Анализ периодов охвата субъектов квазигосударственного сектора контрольными мероприятиями СК
     * @param yearFrom
     * @param yearTo
     */
    public void getCoverCtrlObjReport(Integer yearFrom, Integer yearTo) {
        ViewObject ctrlObjGroupVO = getSCtrlObjGroupsView1();
        Row row = ctrlObjGroupVO.getCurrentRow();
        Number ctrlObjGroupId = (Number)row.getAttribute("Id");
        ViewObject coverCtrlObjVO = getGCoverCtrlObjView1();
        coverCtrlObjVO.setWhereClause(" (ID1 = " + ctrlObjGroupId +
                                      ") AND (YEAR BETWEEN " + yearFrom +
                                      " AND " + yearTo + ") ");
        coverCtrlObjVO.executeQuery();
    }
    
    /**
     * Анализ полноты охвата ГО тематическими контрольными мероприятиями СК за 2 года
     * @param ctrlObjId
     * @param year
     */
    public void getPlntdCtrlObjReport(Number ctrlObjId, Integer year) {
        ViewObject plntdCtrlObjVO = getGPlntdThemCtrlView1();
        plntdCtrlObjVO.setWhereClause(" (YEAR BETWEEN " + (year - 1) + " AND " + year + ") " +
                                      " AND CTRL_OBJ_ID = " + ctrlObjId + " ");
        plntdCtrlObjVO.executeQuery();
    }
    
    /**
     * Анализ полноты охвата по всем ГО тематическими контрольными мероприятиями за 2 года
     * @param year
     */
    public void getPlntdAllCtrlObjReport(Integer year) {
        ViewObject ctrlObjGroupVO = getSCtrlObjGroupsView1();
        Row row = ctrlObjGroupVO.getCurrentRow();
        Number ctrlObjGroupId = (Number)row.getAttribute("Id");
        ViewObject plntdCtrlObjVO = getGPlntdThemALLCtrlView1();
        plntdCtrlObjVO.setNamedWhereClauseParam("yearFrom", year - 1);
        plntdCtrlObjVO.setNamedWhereClauseParam("yearTo", year);
        plntdCtrlObjVO.setWhereClause(" (YEAR BETWEEN " + (year - 1) + " AND " + year + ") " +
                                      " AND ID4 = " + ctrlObjGroupId + " ");
        plntdCtrlObjVO.executeQuery();
    }
    
    /**
     * Посдчёт показателей квазигос-ых субъектов
     */
    public void setRiskValuesByFormula(){
        RowSetIterator rsRisksIterator = 
            getGRisksCurrentLink1().getDestination().createRowSetIterator(null);
        while(rsRisksIterator.hasNext()){
            Row riskRow = rsRisksIterator.next();
            Number result = getCalculatedValueByFormula(riskRow.getAttribute("Formula") != null ? riskRow.getAttribute("Formula").toString() : null);
            riskRow.setAttribute("RiskValue", result);
        }
        ViewObject rDataPeriodVO = getRDataPeriodView1();
        rDataPeriodVO.setWhereClause(null);
        rDataPeriodVO.executeQuery();
    }
    
    /**
     * Получение значения по формуле
     * @param formula
     * @return
     */
    private Number getCalculatedValueByFormula(String formula) {
        if (formula == null) {
            return null;
        }
        try {
            StringBuilder ids = new StringBuilder();

            //Получение IDS значений информации квазигос-ых субъектов
            Pattern r = Pattern.compile("\\[\\d+");
            Matcher m = r.matcher(formula);
            String comma = "";
            while (m.find()) {
                ids.append(comma).append(m.group().substring(1, m.group().length()));
                comma = ",";
            }

            //Получение значений информации квазигос-ых субъектов по IDS
            ViewObject rDataPeriodVO = getRDataPeriodView1();
            rDataPeriodVO.setWhereClause(" CURRENT_DATE BETWEEN START_DATE AND END_DATE ");
            rDataPeriodVO.setOrderByClause("ID");
            rDataPeriodVO.executeQuery();
            if (rDataPeriodVO.getRowCount() == 0) {
                return null;
            }
            Row rDataPeriodRow = rDataPeriodVO.last();
            ViewObject rDataValueVO = getRDataValueByFormulaView1();
            rDataValueVO.setWhereClause(" DATA_ID IN (" + ids + ") AND PERIOD_ID = " +
                                        rDataPeriodRow.getAttribute("Id"));
            rDataValueVO.executeQuery();

            //Формирование list из блоков формулы
            Pattern pattern = Pattern.compile("\\[\\d+\\,\\s*\\d+\\]");
            Matcher m2 = pattern.matcher(formula);
            List<String> list = new ArrayList<String>();
            while (m2.find()) {
                list.add(m2.group());
            }

            //Формирование map со значениями квазигос-ых субъектов
            Map<String, BigDecimal> map = new HashMap<String, BigDecimal>();
            for (String s : list) {
                String[] strs = s.split(",");
                String param1 = strs[0].substring(1, strs[0].length()).trim();
                String param2 = strs[1].substring(0, strs[1].length() - 1).trim();

                RowSetIterator iterator = rDataValueVO.createRowSetIterator(null);
                while (iterator.hasNext()) {
                    Row row = iterator.next();
                    if (row.getAttribute("DataId").toString().equals(param1)) {
                        map.put(s, new BigDecimal(param2.equals("1") ? row.getAttribute("BeginValue").toString() :
                                           row.getAttribute("EndValue").toString()));
                    }
                }
            }

            //Формирование формулы для расчёта значения
            for (String key : map.keySet()) {
                formula = formula.replaceAll(key.replace("]","\\]").replace("[","\\["),
                                           map.get(key).toString());
            }

            //Валидация формулы
            if(formula.contains("[")){
                return null;
            }
            //Расчёт значения
            Calculable calc = new ExpressionBuilder(formula).build();
            return new Number(calc.calculate());
        } catch (Exception e) {
            return null;
        }
    }
    
    /**
     * Получение списка значений рисков(текстовые значения)
     * @param riskId
     */
    public void getRiskListValuesByRiskId(Number riskId){
        ViewObject riskListValuesVO = getSRiskListValuesView2();
        riskListValuesVO.setWhereClause(" RISK_ID = " + riskId + "");
        riskListValuesVO.executeQuery();
    }
    
    /**
     * Создание нового периода
     * @param year Год нового периода
     * @return
     */
    public Number savePeriod(Number year) {
        SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
        
        MonthListViewImpl monthListVO = (MonthListViewImpl) getMonthListView1();
        Date startDate = monthListVO.getSelectedRange(year).get(monthListVO.START_DATE);
        Date endDate = monthListVO.getSelectedRange(year).get(monthListVO.END_DATE);
        
        ViewObject rDataPeriodVO = getCtrlObjDataPeriodViewLink1().getDestination();
        rDataPeriodVO.setWhereClause(
            "trunc(START_DATE) = to_date('" + sdf.format(startDate) + "', 'dd-mm-yyyy') AND "
            + "trunc(END_DATE) = to_date('" + sdf.format(endDate) + "', 'dd-mm-yyyy')"
        );
        rDataPeriodVO.executeQuery();
        
        Number periodId = null;
        Row newRow = null;
        if (rDataPeriodVO.getRowCount() > 0) {
            newRow = rDataPeriodVO.getRowSet().first();
            periodId = (Number) newRow.getAttribute("Id");
        } else {
            newRow = rDataPeriodVO.createRow();
            periodId = getRDataPeriodNextSeq();
            newRow.setAttribute("Id", periodId);
            newRow.setAttribute("StartDate", startDate);
            newRow.setAttribute("EndDate", endDate);
            newRow.setAttribute("CreateDate", new Date());
        }
        
        return periodId;
    }
    
    /**
     * Сохранение редактированных данных
     * по квазигосударственным субъектам
     * @param year
     */
    public void saveEditedFilledDatas(Number year) {
        Number periodId = savePeriod(year);
        
        ViewObject rDataValue1VO = getRDataValue1View1();
        ViewObject rDataValue2VO = getRDataValue2View1();
        
        RowSetIterator rs = getSPublicDataView1().createRowSetIterator(null);
        while (rs.hasNext()) {
            Row publicDataRow = rs.next();
            java.lang.Number beginValue = (java.lang.Number) publicDataRow.getAttribute("BeginValue");
            java.lang.Number endValue = (java.lang.Number) publicDataRow.getAttribute("EndValue");
            String value = (String) publicDataRow.getAttribute("Value");
            String value2 = (String) publicDataRow.getAttribute("Value2");
            Number value1Id = (Number) publicDataRow.getAttribute("Value1Id");
            Number value2Id = (Number) publicDataRow.getAttribute("Value2Id");
            Row[] value1Rows = rDataValue1VO.findByKey(new Key(new Object[]{value1Id}), -1);
            Row[] value2Rows = rDataValue2VO.findByKey(new Key(new Object[]{value2Id}), -1);
            
            if (beginValue != null || endValue != null) {
                Row newVal1Row = null;
                if (value1Rows.length > 0) {
                    newVal1Row = value1Rows[0];
                } else {
                    newVal1Row = rDataValue1VO.createRow();
                    newVal1Row.setAttribute("Id", getRDataValue1NextSeq());
                }
                newVal1Row.setAttribute("DataId", publicDataRow.getAttribute("Id"));
                newVal1Row.setAttribute("PeriodId", periodId);
                newVal1Row.setAttribute("BeginValue", beginValue);
                newVal1Row.setAttribute("EndValue", endValue);
            }
            
            if (value != null && value2 != null) {
                Row newVal2Row = null;
                if (value2Rows.length > 0) {
                    newVal2Row = value2Rows[0];
                } else {
                    newVal2Row = rDataValue2VO.createRow();
                    newVal2Row.setAttribute("Id", getRDataValue2NextSeq());
                }
                newVal2Row.setAttribute("DataId", publicDataRow.getAttribute("Id"));
                newVal2Row.setAttribute("PerionId", periodId);
                newVal2Row.setAttribute("Value", value);
                newVal2Row.setAttribute("Value2", value2);
            }
        }
        getTransaction().commit();
    }
    
    /**
     * Сохранение всех введеных значений
     * по квазигосударственным субъектам
     * @param year 
     */
    public void saveDataValue(Number year) {
        Number periodId = savePeriod(year);
        
        ViewObject rDataValue1VO = getRDataValue1View1();
        ViewObject rDataValue2VO = getRDataValue2View1();
        
        RowSetIterator rs = getSPublicDataView1().createRowSetIterator(null);
        while (rs.hasNext()) {
            Row publicDataRow = rs.next();
            if (publicDataRow.getAttribute("BeginValue") != null || publicDataRow.getAttribute("EndValue") != null) {
                Row newRow = rDataValue1VO.createRow();
                newRow.setAttribute("Id", getRDataValue1NextSeq());
                newRow.setAttribute("DataId", publicDataRow.getAttribute("Id"));
                newRow.setAttribute("PeriodId", periodId);
                newRow.setAttribute("BeginValue", publicDataRow.getAttribute("BeginValue"));
                newRow.setAttribute("EndValue", publicDataRow.getAttribute("EndValue"));
            }
            if (publicDataRow.getAttribute("Value") != null
                    && publicDataRow.getAttribute("Value2") != null) {
                Row rDataValue2Row = rDataValue2VO.createRow();
                rDataValue2Row.setAttribute("Id", getRDataValue2NextSeq());
                rDataValue2Row.setAttribute("Value", publicDataRow.getAttribute("Value"));
                rDataValue2Row.setAttribute("Value2", publicDataRow.getAttribute("Value2"));
                rDataValue2Row.setAttribute("PerionId", periodId);
                rDataValue2Row.setAttribute("DataId", publicDataRow.getAttribute("Id"));
            }
        }
        getTransaction().commit();
    }
    
    /**
     * Загрузка всех данных для отображения
     * значений по выбранному периоду.
     */
    public void loadQuasiDataVaues() {
        ViewObject publicDataVO = getSPublicDataTypePublicDataViewLink1().getDestination();
        RowSetIterator rs = publicDataVO.getRowSet().createRowSetIterator(null);
        
        Row periodRow = getRDataPeriodView1().getCurrentRow();
        Number num = (Number) periodRow.getAttribute("Id");
        
        if (periodRow.getAttribute("Id") != null) {
            MonthListViewImpl vo = (MonthListViewImpl) getMonthListView1();
            Date startDate = ((oracle.jbo.domain.Date)periodRow.getAttribute("StartDate")).dateValue();
            Date endDate = ((oracle.jbo.domain.Date)periodRow.getAttribute("EndDate")).dateValue();
            Integer id = vo.getIdByPeriodRange(startDate, endDate);
            vo.setCurrentRow(vo.findByKey(new Key(new Object[]{id}), 1)[0]);
        }

        ViewObject value1VO = getRDataValue1View1();
        value1VO.setWhereClause("PERIOD_ID = " + num);
        value1VO.setOrderByClause("DATA_ID");
        value1VO.executeQuery();
        
        ViewObject value2VO = getRDataValue2View1();
        value2VO.setWhereClause("PERION_ID = " + num);
        value2VO.setOrderByClause("DATA_ID");
        value2VO.executeQuery();
        
        while(rs.hasNext()) {
            Row publicDataRow = rs.next();
            while (value1VO.hasNext()) {
                Row val1Row = value1VO.next();
                if (val1Row.getAttribute("DataId").equals(publicDataRow.getAttribute("Id"))) {
                    publicDataRow.setAttribute("Value1Id", val1Row.getAttribute("Id"));
                    publicDataRow.setAttribute("BeginValue", val1Row.getAttribute("BeginValue"));
                    publicDataRow.setAttribute("EndValue", val1Row.getAttribute("EndValue"));
                }
                
            }
            while (value2VO.hasNext()) {
                Row val2Row = value2VO.next();
                if (val2Row.getAttribute("DataId").equals(publicDataRow.getAttribute("Id"))) {
                    publicDataRow.setAttribute("Value2Id", val2Row.getAttribute("Id"));
                    publicDataRow.setAttribute("Value", val2Row.getAttribute("Value"));
                    publicDataRow.setAttribute("Value2", val2Row.getAttribute("Value2"));
                }
            }
            value1VO.reset();
            value2VO.reset();
        }
    }
    
    /**
     * Get sequence by name
     * @param seqName
     * @return
     */
    private Number getSequenceId(String seqName) {
        SequenceImpl s =
            new SequenceImpl(seqName, getDBTransaction());
        return s.getSequenceNumber();
    }
    
    /**
     * Get risk calcs next sequence
     * @return
     */
    private Number getRiskCalcsNextSeq() {
        return getSequenceId(GRiskCalcsModelImpl.G_RISK_CALCS_SEQ);
    }
    
    /**
     * Get risk calcs object next sequence
     * @return
     */
    private Number getRiskCalcsObjNextSeq() {
        return getSequenceId(GRisksCalcsObjModelImpl.G_RISKS_CALCS_OBJ_SEQ);
    }
    
    /**
     * Get GRisk values next sequence
     * @return
     */
    private Number getGRiskValuesNextSeq() {
        return getSequenceId(GRiskValuesModelImpl.G_RISK_VALUES_SEQ);
    }  
    
    /**
     * Get risks next sequence
     * @return
     */
    private Number getRiskNextSeq() {
        return getSequenceId(GRisksModelImpl.RISK_SEC_NAME);
    }
    
    /**
     * Get risk values next sequence
     * @return
     */
    private Number getRiskValNextSeq() {
        return getSequenceId(SRiskValuesModelImpl.RISK_VAL_SEC_NAME);
    }
    
    /**
     * Get risk values list next sequence
     * @return
     */
    private Number getRiskListValNextSeq() {
        return getSequenceId(SRiskListValuesModelImpl.RISK_LIST_VAL_SEC_NAME);
    }
    
    /**
     * Get Data Period next sequence
     * @return
     */
    private Number getRDataPeriodNextSeq() {
        return getSequenceId(RDataPeriodModelImpl.DATA_PERIOD_SEC_NAME);
    }
    
    /**
     * Get Data Value1 next sequence
     * @return
     */
    private Number getRDataValue1NextSeq() {
        return getSequenceId(RDataValue1ModelImpl.DATA_VALUE1_SEC_NAME);
    }
    
    /**
     * Get Data Value2 next sequence
     * @return
     */
    private Number getRDataValue2NextSeq() {
        return getSequenceId(RDataValue2ModelImpl.DATA_VALUE2_SEC_NAME);
    }

    protected void prepareSession(Session session) {
        super.prepareSession(session);
        // do session-specific initializations
        this.callStroredProcedure();
    }

    protected void callStroredProcedure() {
        LOGGER.debug("START");
        PreparedStatement st = null;
        int updatedRow = 0;
        try {
            String stmt = "begin pkg_audit.setUserName('" + this.getUserName() + "'); end;";
            st = getDBTransaction().createPreparedStatement(stmt, 0);
            updatedRow = st.executeUpdate();
        } catch (SQLException e) {
            LOGGER.error(e);
            throw new JboException(e);
        } catch (Exception e) {
            LOGGER.error(e);
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    LOGGER.error(e);
                    e.printStackTrace();
                }
            }
        }
        LOGGER.debug("FINISH");
    }

    public static String getUserName() {
        LOGGER.debug("START");
        ADFContext adfContext = ADFContext.getCurrent();
        String userName = adfContext.getSecurityContext().getUserName();
        LOGGER.debug("userName - " + userName);
        return userName;
//        LOGGER.debug("FINISH");
    }

    /**
     * Container's getter for GRisksArchiveParentView1.
     * @return GRisksArchiveParentView1
     */
    public ViewObjectImpl getGRisksArchiveParentView1() {
        return (ViewObjectImpl)findViewObject("GRisksArchiveParentView1");
    }
}
